"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证invisible/visible索引
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表
    3、创建index
    4、explain查询计划信息
    5、修改index为invisible查询计划信息
    6、修改index为invisible查询计划信息
    7、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、创建成功
    4、查询不走索引
    5、index为invisible查询走索引
    6、index为invisible查询不走索引
    7、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_26"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.c_tab = f"drop table if exists tab_protocol_0026; " \
                     f"create table tab_protocol_0026(col1 int, " \
                     f"col2 text,col3 numeric); "
        self.insert = f"insert into tab_protocol_0026 values( generate_series(1,100000)," \
                      f"'test'||round(random()*100,2),round(random()*100,2)); "
        self.c_index = f"drop index if exists idx_protocol_0026;" \
                       f"create index idx_protocol_0026 on tab_protocol_0026 " \
                       f"(col1) invisible; "
        self.explain1 = f"explain(costs false) select col1 from tab_protocol_0026 where col1<=19981 and col3>=94;"
        self.alter1 = f"alter table tab_protocol_0026 alter index idx_protocol_0026 visible; "
        self.explain2 = f"explain(costs false) select col1 from tab_protocol_0026 where col1>=74361 and col3<=11;"
        self.alter2 = f"alter table tab_protocol_0026 alter index idx_protocol_0026 invisible; "
        self.explain3 = f"explain(costs false) select col1 from tab_protocol_0026 where col1>34567 and col3>87;"
        self.clean_tb = f"drop index if exists idx_protocol_0026; " \
                        f"drop table if exists tab_protocol_0026 cascade constraints;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(sql=self.c_tab, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)

        text = '--step3:M*连接数据库创建index;expect:创建成功--'
        logger.info(text)
        c_res = my_sh.execut_db_sql(sql=self.c_index, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        c_res = my_sh.execut_db_sql(sql=self.insert, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)

        text = '--step4:M*连接数据库explain查询计划信息;expect:不执行该索引 --'
        logger.info(text)
        show_res = my_sh.execut_db_sql(sql=self.explain1, dbname=self.db_name, host=self.pri_node.db_host,
                                       port=my_port, user=self.user, password=self.password)
        logger.info(show_res)
        self.assertNotIn('Index Scan', show_res, '执行失败' + text)

        text = '--step5:M*连接数据库修改index为visible查询计划信息;expect:执行该索引 --'
        logger.info(text)
        al_res = my_sh.execut_db_sql(sql=self.alter1, dbname=self.db_name, host=self.pri_node.db_host,
                                     port=my_port, user=self.user, password=self.password)
        logger.info(al_res)
        self.assertNotIn('ERROR', al_res, '执行失败' + text)
        show_res = my_sh.execut_db_sql(sql=self.explain2, dbname=self.db_name, host=self.pri_node.db_host,
                                       port=my_port, user=self.user, password=self.password)
        logger.info(show_res)
        self.assertIn('Index Scan', show_res, '执行失败' + text)

        text = '--step6:M*连接数据库修改index为invisiable查询计划信息;expect:不执行该索引 --'
        logger.info(text)
        al_res = my_sh.execut_db_sql(sql=self.alter2, dbname=self.db_name, host=self.pri_node.db_host,
                                     port=my_port, user=self.user, password=self.password)
        logger.info(al_res)
        self.assertNotIn('ERROR', al_res, '执行失败' + text)
        show_res = my_sh.execut_db_sql(sql=self.explain3, dbname=self.db_name, host=self.pri_node.db_host,
                                       port=my_port, user=self.user, password=self.password)
        logger.info(show_res)
        self.assertNotIn('Index Scan', show_res, '执行失败' + text)

    def tearDown(self):
        text = '--step7:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
